﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using System.IO;

namespace Auxiliar
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            XNamespace aw = "http://earth.google.com/kml/2.1";

            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Km .km|*.*";

                   
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                XDocument documeto = XDocument.Load(dlg.FileName);
//Descendants("kml").Descendants("Document").Descendants("Placemark").Descendants("LineString").
                var lineString = (from item in documeto.Descendants(aw + "LineString")
                                  select new { Linhas = item.Element(aw + "coordinates").Value.Split("\r\n".ToArray()).ToArray() }).ToList();
                using (StreamWriter wr = new StreamWriter("Insert posicao.sql"))
                {
                    DateTime data = new DateTime(2011, 1, 3);
                    foreach (var item in lineString)
                    {
                        data = data.AddDays(6);

                        foreach (String str in item.Linhas)
                        {
                            if (string.IsNullOrEmpty(str) || string.IsNullOrEmpty(str.Trim())) continue;

                            string[] valores = str.Trim().Split(',');

                            string latitude = valores[0];
                            string longitude = valores[1];
                            string velocidade = valores[2];


                            wr.WriteLine(string.Format("insert into Posicoes values('{0}','{1}',{2},1,'A',{3});", longitude, latitude, data.ToString("dd/MM/yyy"), velocidade));
                            wr.Flush();

                        }
                    }
                }
            }

            
        }
    }
}
